package com.weilan.ark3.sqlite;

import com.weilan.ark3.controller.ExcelFaultController;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
@Component
public class SQLiteUtils {

    @Autowired
    private ExcelFaultController excelFaultController;
    public synchronized void insertToDB(String tableName, List<String> tableList, String fieldsStr) {
        try {
            //连接SQLite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名weilan.db的连接，如果不存在就在当前目录下创建之
            Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/db/ARK3/ark3.0.db");
            Statement stat = conn.createStatement();
            StringBuffer filedStr = new StringBuffer();
            for (int i = 0; i < tableList.size(); i++) {
                String appStr = i == tableList.size() - 1 ? " varchar(20)" : " varchar(20),";
                filedStr.append(tableList.get(i) + appStr);
            }
            String creatTableSql = "create table if not exists %s(%s);";
            //创建表
            String createSql = String.format(creatTableSql, tableName, filedStr);
            stat.executeUpdate(createSql);

            String insertSql = "insert into %s values(%s);";
            //插入数据
            String format = String.format(insertSql, tableName, fieldsStr);
            stat.executeUpdate(format);
            conn.close();
        } catch (Exception e) {
//            //删除之前的表，重新建表
            excelFaultController.dataToExcel(tableName);
            int i = SQLiteUtils.deleteTable(tableName);
            if (i == 0) {
                e.printStackTrace();
            }
//            e.printStackTrace();
        }
    }


    /*
     * 删除数据库单张表
     * */
    public static int deleteTable(String tableName) {
        int i = 0;
        try {
            //连接SQLite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名weilan.db的连接，如果不存在就在当前目录下创建之
            Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/db/ARK3/ark3.0.db");
            Statement statement = conn.createStatement();
            //删表之前先查有无该表
            String deleteTableSql = "DROP TABLE IF EXISTS %s;";
            //创建表
            String deleteSql = String.format(deleteTableSql, tableName);
            statement.executeUpdate(deleteSql);
            statement.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 查询数据库多少张表
     */
    public static List<String> selectFromDB() {
        List<String> deleteList = new ArrayList<>();
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //连接sqlite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名weilan.db的连接，如果不存在就在当前目录下创建之
            conn = DriverManager.getConnection("jdbc:sqlite:C:/db/ARK3/ark3.0.db");
            String sqlTable = "SELECT * FROM sqlite_master WHERE type='table' AND name NOT IN ('tb_modbus_conf', 'tb_control_rtu', 'tb_data','tb_basic','tb_basic_config');";
            //创建查询语句
            statement = conn.createStatement();
            //执行查询语句
            resultSet = statement.executeQuery(sqlTable);
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                deleteList.add(name);
            }

            //关闭连接
            resultSet.close();
            statement.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return deleteList;
    }

    /**
     * 删除主数据库表
     */
    public static void deleteFromDB(List<String> dltNameList) {
        try {
            //连接SQLite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名weilan.db的连接，如果不存在就在当前目录下创建之
            Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/db/ARK3/ark3.0.db");
            Statement statement = conn.createStatement();
            //创建一个存储删除表名的sql语句集合
            //  List<String> deleteTableNameSQLList = new ArrayList<>();
            //删除表语句的前缀
            String deleteSQL = "DROP TABLE %s ";
            //遍历拿到表名品拼接成sql语句。
            /*  String deleteTableNameSQLStr = String.join(",", deleteTbNameList);
            String[] deleteTableNameSQLArr = deleteTableNameSQLStr.split(",");
            for (int i = 0; i < deleteTableNameSQLArr.length; i++) {
                String str = deleteSQLPrefix + deleteTableNameSQLArr[i];
                deleteTableNameSQLList.add(str);
            }*/
            for (int i = 0; i < dltNameList.size(); i++) {
                String tbName = dltNameList.get(i);
                String format = String.format(deleteSQL, tbName);
                statement.executeUpdate(format);
            }
           /* //遍历sql语句一个一个删除
            for (String sql : deleteTableNameSQLList) {
                statement.executeUpdate(sql);
            }*/
            statement.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}